﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class AuditDefinitionDA : IAuditDefinitionDA
    {
        private string COL_NAME = "@COL_NAME";
        private string COL_TYPE = "@COL_TYPE";
        private string FOR_DELETE = "@FOR_DELETE";
        private string FOR_INSERT = "@FOR_INSERT";
        private string FOR_UPDATE = "@FOR_UPDATE";
        private string SQL_CHECK_AUDIT_DEFINITION_ID_UNIQUE = " SELECT COUNT(1) FROM @_@AUDIT_DEFINITION WHERE TABLE_NAME = @TABLE_NAME AND COL_NAME = @COL_NAME ";
        private string SQL_DELETE_AUDIT_DEFINITION = " DELETE FROM @_@AUDIT_DEFINITION WHERE TABLE_NAME = @TABLE_NAME AND COL_NAME = @COL_NAME ";
        private string SQL_GET_AUDIT_DEFINITION_BY_TABLENAME_AND_ACTION = " SELECT AD.COL_NAME, ASE.MAPPING_NAME, ASE.IS_PRIMARY FROM @_@AUDIT_DEFINITION AD  LEFT JOIN @_@AUDIT_SCHEMA ASE ON AD.TABLE_NAME = ASE.TABLE_NAME AND AD.COL_NAME = ASE.COL_NAME  WHERE AD.TABLE_NAME = @TABLE_NAME AND @AUDITTYPE@ ";
        private string SQL_GET_PRIMARY_KEY_BY_TABLE_NAME = " SELECT [COL_NAME], MAPPING_NAME FROM @_@AUDIT_SCHEMA WHERE TABLE_NAME = @TABLE_NAME AND IS_PRIMARY = 'Y' ORDER BY PK_ORDER ";
        private string SQL_INSERT_AUDIT_DEFINITION = " INSERT INTO @_@AUDIT_DEFINITION ( TABLE_NAME, COL_NAME, COL_TYPE, FOR_INSERT, FOR_UPDATE, FOR_DELETE) VALUES ( @TABLE_NAME, @COL_NAME, @COL_TYPE, @FOR_INSERT, @FOR_UPDATE, @FOR_DELETE, @FOR_DELETE)  ";
        private string SQL_SELECT_ALL_AUDIT_DEFINITION = " SELECT  AD.ROWID, AD.TABLE_NAME, AD.COL_NAME, AS1.IS_PRIMARY, AD.COL_TYPE, AD.FOR_INSERT, AD.FOR_UPDATE, AD.FOR_DELETE  FROM  @_@AUDIT_DEFINITION AD LEFT JOIN @_@AUDIT_SCHEMA AS1 ON AD.TABLE_NAME = AS1.TABLE_NAME AND AD.COL_NAME = AS1.COL_NAME ";
        private string SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT = "SELECT * FROM (  SELECT  AD.ROWID, AD.TABLE_NAME, AD.COL_NAME, AS1.IS_PRIMARY, AD.COL_TYPE, AD.FOR_INSERT, AD.FOR_UPDATE, AD.FOR_DELETE  FROM  @_@AUDIT_DEFINITION AD LEFT JOIN @_@AUDIT_SCHEMA AS1 ON AD.TABLE_NAME = AS1.TABLE_NAME AND AD.COL_NAME = AS1.COL_NAME  ) TEMP ";
        private string SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT_TABLENAME = " SELECT DISTINCT TABLE_NAME FROM (  SELECT  AD.ROWID, AD.TABLE_NAME, AD.COL_NAME, AS1.IS_PRIMARY, AD.COL_TYPE, AD.FOR_INSERT, AD.FOR_UPDATE, AD.FOR_DELETE  FROM  @_@AUDIT_DEFINITION AD LEFT JOIN @_@AUDIT_SCHEMA AS1 ON AD.TABLE_NAME = AS1.TABLE_NAME AND AD.COL_NAME = AS1.COL_NAME  ) TEMP  ";
        private string SQL_UPDATE_AUDIT_DEFINITION = " UPDATE @_@AUDIT_DEFINITION SET COL_TYPE = @COL_TYPE, FOR_INSERT = @FOR_INSERT, FOR_UPDATE = @FOR_UPDATE, FOR_DELETE = @FOR_DELETE WHERE TABLE_NAME = @TABLE_NAME AND COL_NAME = @COL_NAME  ";
        private string TABLE_NAME = "@TABLE_NAME";

        public DataSet GetAllAuditDefinition(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllAuditDefinition(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public AuditDefinitionInfo GetAuditDefinitionByID(string tableName, string colName, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION.Replace("@_@", whLoginID) + " WHERE TABLE_NAME = @TABLE_NAME AND COL_NAME = @COL_NAME  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName), new SqlParameter("COL_NAME", colName) };
            AuditDefinitionInfo auditDefinitionInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    auditDefinitionInfo = new AuditDefinitionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["TABLE_NAME"].ToString(), reader["COL_NAME"].ToString(), reader["IS_PRIMARY"].ToString(), reader["COL_TYPE"].ToString(), reader["FOR_INSERT"].ToString(), reader["FOR_UPDATE"].ToString(), reader["FOR_DELETE"].ToString());
                }
            }
            return auditDefinitionInfo;
        }

        public AuditDefinitionInfo GetAuditDefinitionByID(DataBase dataBase, DbTransaction tran, string tableName, string colName, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION.Replace("@_@", whLoginID) + " WHERE TABLE_NAME = @TABLE_NAME AND COL_NAME = @COL_NAME  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName), new SqlParameter("COL_NAME", colName) };
            AuditDefinitionInfo auditDefinitionInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                auditDefinitionInfo = new AuditDefinitionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["TABLE_NAME"].ToString(), reader["COL_NAME"].ToString(), reader["IS_PRIMARY"].ToString(), reader["COL_TYPE"].ToString(), reader["FOR_INSERT"].ToString(), reader["FOR_UPDATE"].ToString(), reader["FOR_DELETE"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return auditDefinitionInfo;
        }

        public DataSet GetAuditDefinitionByQueryList(List<string> sqlWhere, AuditDefinitionQueryEntity auditdefinitionQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT_TABLENAME.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!auditdefinitionQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, auditdefinitionQuery.CurrentPage, auditdefinitionQuery.PageSize, auditdefinitionQuery.SortField, auditdefinitionQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetAuditDefinitionByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, AuditDefinitionQueryEntity auditdefinitionQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT_TABLENAME.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!auditdefinitionQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, auditdefinitionQuery.CurrentPage, auditdefinitionQuery.PageSize, auditdefinitionQuery.SortField, auditdefinitionQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<AuditDefinitionInfo> GetAuditDefinitionByTableName(string tableName, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT.Replace("@_@", whLoginID) + " WHERE TABLE_NAME = @TABLE_NAME ORDER BY [COL_NAME] ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            List<AuditDefinitionInfo> auditDefinitionInfos = new List<AuditDefinitionInfo>();
            AuditDefinitionInfo auditDefinitionInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    auditDefinitionInfo = new AuditDefinitionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["TABLE_NAME"].ToString(), reader["COL_NAME"].ToString(), reader["IS_PRIMARY"].ToString(), reader["COL_TYPE"].ToString(), reader["FOR_INSERT"].ToString(), reader["FOR_UPDATE"].ToString(), reader["FOR_DELETE"].ToString());
                    auditDefinitionInfos.Add(auditDefinitionInfo);
                }
            }
            return auditDefinitionInfos;
        }

        public List<AuditDefinitionInfo> GetAuditDefinitionByTableName(DataBase dataBase, DbTransaction tran, string tableName, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_AUDIT_DEFINITION_DISTINCT.Replace("@_@", whLoginID) + " WHERE TABLE_NAME = @TABLE_NAME ORDER BY [COL_NAME] ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            List<AuditDefinitionInfo> auditDefinitionInfos = new List<AuditDefinitionInfo>();
            AuditDefinitionInfo auditDefinitionInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                auditDefinitionInfo = new AuditDefinitionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["TABLE_NAME"].ToString(), reader["COL_NAME"].ToString(), reader["IS_PRIMARY"].ToString(), reader["COL_TYPE"].ToString(), reader["FOR_INSERT"].ToString(), reader["FOR_UPDATE"].ToString(), reader["FOR_DELETE"].ToString());
                auditDefinitionInfos.Add(auditDefinitionInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return auditDefinitionInfos;
        }

        public DataSet GetAuditDefinitionByTableNameAndAction(string tableName, string auditAction, string whLoginID)
        {
            string sql = this.SQL_GET_AUDIT_DEFINITION_BY_TABLENAME_AND_ACTION.Replace("@_@", whLoginID);
            string CS40001 = auditAction.ToUpper();
            if (CS40001 != null)
            {
                if (!(CS40001 == "INSERT"))
                {
                    if (CS40001 == "UPDATE")
                    {
                        sql = sql.Replace("@AUDITTYPE@", "FOR_UPDATE = 'Y'");
                    }
                    else if (CS40001 == "DELETE")
                    {
                        sql = sql.Replace("@AUDITTYPE@", "FOR_DELETE = 'Y'");
                    }
                }
                else
                {
                    sql = sql.Replace("@AUDITTYPE@", "FOR_INSERT = 'Y'");
                }
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAuditDefinitionByTableNameAndAction(DataBase dataBase, DbTransaction tran, string tableName, string auditAction, string whLoginID)
        {
            string sql = this.SQL_GET_AUDIT_DEFINITION_BY_TABLENAME_AND_ACTION.Replace("@_@", whLoginID);
            string CS40001 = auditAction.ToUpper();
            if (CS40001 != null)
            {
                if (!(CS40001 == "INSERT"))
                {
                    if (CS40001 == "UPDATE")
                    {
                        sql = sql.Replace("@AUDITTYPE@", "FOR_UPDATE = 'Y'");
                    }
                    else if (CS40001 == "DELETE")
                    {
                        sql = sql.Replace("@AUDITTYPE@", "FOR_DELETE = 'Y'");
                    }
                }
                else
                {
                    sql = sql.Replace("@AUDITTYPE@", "FOR_INSERT = 'Y'");
                }
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetPrimaryKeyByTableName(string tableName, string whLoginID)
        {
            string sql = this.SQL_GET_PRIMARY_KEY_BY_TABLE_NAME.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrimaryKeyByTableName(DataBase dataBase, DbTransaction tran, string tableName, string whLoginID)
        {
            string sql = this.SQL_GET_PRIMARY_KEY_BY_TABLE_NAME.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TABLE_NAME", tableName) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        private SqlParameter[] Set_AuditDefinition_Parameters(AuditDefinitionInfo auditDefinitionInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.TABLE_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.COL_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.COL_TYPE, SqlDbType.VarChar, 15), new SqlParameter(this.FOR_INSERT, SqlDbType.VarChar, 1), new SqlParameter(this.FOR_UPDATE, SqlDbType.VarChar, 1), new SqlParameter(this.FOR_DELETE, SqlDbType.VarChar, 1) };
            paramArray[0].Value = auditDefinitionInfo.TableName;
            paramArray[1].Value = auditDefinitionInfo.ColName;
            if (!string.IsNullOrEmpty(auditDefinitionInfo.ColType))
            {
                paramArray[2].Value = auditDefinitionInfo.ColType;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            paramArray[3].Value = auditDefinitionInfo.ForInsert;
            if (!string.IsNullOrEmpty(auditDefinitionInfo.ForUpdate))
            {
                paramArray[4].Value = auditDefinitionInfo.ForUpdate;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(auditDefinitionInfo.ForDelete))
            {
                paramArray[5].Value = auditDefinitionInfo.ForDelete;
                return paramArray;
            }
            paramArray[5].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateAuditDefinition(AuditDefinitionInfo auditDefinitionInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = this.Set_AuditDefinition_Parameters(auditDefinitionInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_AUDIT_DEFINITION.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateAuditDefinition(DataBase dataBase, DbTransaction tran, AuditDefinitionInfo auditDefinitionInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_AuditDefinition_Parameters(auditDefinitionInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_AUDIT_DEFINITION.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

